#!/usr/bin/perl

#NOTE: IN ORDER TO USE THIS SCRIPT, YOU MUST FIRST QUERY A SET OF ALL BIB RECORDS

#THEN YOU MUST "EXPORT" INFORMATION FROM CATALOG
#for instance:

#   RECORD #(BIBLIO)

#THE RESULTING TEXT FILE NEEDS TO BE LOADED INTO ./bibs_deleted/

use Cwd;

$baseurl = getcwd;

if ($ARGV[0] eq 'CREATING') {
    $mode = 'CREATING';
} else {
    $mode = 'UPDATING';
}

if ($ARGV[1] =~ /\//) {
    ($baseurl = $ARGV[1]) =~ s/[\/]*$//;
}

$error_outs = $baseurl . '/errors_bibs_deleted.txt';
$print_outs = $baseurl . '/prints_bibs_deleted.txt';

binmode(STDOUT, ':utf8');

open (STDERR, ">$error_outs");
open (STDOUT, ">$print_outs");

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

use IO::File;

&hashes_and_arrays; #SUBROUTINE AT END OF SCRIPT

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_time = "$HOUR:$MIN:$SEC";

$where = $baseurl . '/bibs_deleted';

##START: GET BIB NUMBERS OF DELETED RECORDS

use DBI;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 =>1 }) or die "couldn't connect to database: " . DBI->errstr;

$this_table = $db_local_tables_hash{'bibs_record_table'};

$first_file_name = $where . '/undeletedbibs1000000.out';
if(-e $first_file_name) {

        $querystring = "SELECT substr( local_control_number, 6 )
                        FROM $this_table
                        WHERE bib_status = 'x'";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

        while (@row = $query->fetchrow_array) {
            $already_deleted{$row[0]} = "$row[0]";
        }
}

@current_time = localtime;

##END: GET BIB NUMBERS OF DELETED RECORDS

###START: UPDATE $this_table TABLE LINES

while (defined($next_file = <$where/*.out>)) {

print $next_file . "\n";

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

($startoffile = $next_file) =~ s/$where\/undeletedbibs//;
$startoffile =~ s/\.out//;
$next_bib_record = $startoffile;
$endoffile = $next_bib_record + $filename;

    while(<IN>) {

        if($_ !~ /^RECORD #/) {

        #THIS STARTS WHERE THE DELETED BIB RECORDS ARE LOADED
        $this_bib_record = substr($_, 1, 7);

            while($next_bib_record < $this_bib_record) {

                if($already_deleted{$next_bib_record}) {
                    delete($already_deleted{$next_bib_record});
                } else {
                    $update_bib_record = $institution . 'b' . $next_bib_record;
                    $querystring = "UPDATE $this_table
                                    set bib_status = 'x'
                                    WHERE local_control_number = ?";

                    $query = $dbh->prepare($querystring)
                        or die("couldn't prepare statement: ". $dbh->errstr);
    
                    $query->execute($update_bib_record)
                        or die("couldn't execute statement: ". $dbh->errstr);
                }

                $next_bib_record++;

            }

        #THIS ENDS WHERE THE DELETED BIB RECORDS ARE LOADED

        } #END OF if($_ !~ /^RECORD #/)

        $next_bib_record++;

        unlink($next_file);
    }

    close(IN) || "cannot close $next_file: $!";

            while ($next_bib_record < $endoffile) {

                if($already_deleted{$next_bib_record}) {
                    delete($already_deleted{$next_bib_record});
                } else {
                $update_bib_record = $institution . 'b' . $next_bib_record;
                $querystring = "UPDATE $this_table
                                set bib_status = 'x'
                                WHERE local_control_number = ?";

                $query = $dbh->prepare($querystring)
                    or die("couldn't prepare statement: ". $dbh->errstr);

                $query->execute($update_bib_record)
                    or die("couldn't execute statement: ". $dbh->errstr);

                $query->finish();

                }
    
                $next_bib_record++;

            }

}

###END: UPDATE $this_table TABLE LINES

####START: CREATING FILES FOR LOAD INTO LOCAL HOLDINGS TABLE AND CONSORTIUM BIBS AND HOLDINGS TABLES

if ($consortium ne '') {

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_external_update_time = "$HOUR:$MIN:$SEC";

}

$last_update_time = ($current_time[5] + 1900) . "-" . ($current_time[4] + 1) . "-$current_time[3] $current_time[2]:$current_time[1]:$current_time[0]";

print "last_update_time: " . $last_update_time . "\n";

$this_table = $db_local_tables_hash{'bibs_record_table'};
$recordcount = 0;

$position = 0;

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@fields_array, $row[0]); ## field name

        if(exists ($bibs2holdings_hash{ $row[0] })) {
            $bibs2holdings_hash{$row[0]} = $position;
        }

        $position++;
    }

        $query->finish();

@holdings_keys = qw(local_control_number universal_identifier worktitles holding_status formats hloc record_owner);

pop(@fields_array);

    $fields_array_string = join(", ", @fields_array);
    $mysql_holdings_keys = join(", ", @holdings_keys);

$holdings_keys_deleted = $baseurl . '/holdings_deleted/holdings.key';

$holdings_keys_loader = IO::File->new("> $holdings_keys_deleted") || die "cannot open $holdings_keys_deleted for writing: $!\n";
binmode( $holdings_keys_loader, ':utf8' );
flock($holdings_keys_loader, 2);

print $holdings_keys_loader $mysql_holdings_keys . "\n";

        $holdings_keys_loader->autoflush(1);
        close($holdings_keys_loader, 2) || "cannot close $holdings_keys_loader: $!";

if ($consortium ne '') {
$consortium_holdings_keys_deleted = $baseurl . '/consortium_holdings_deleted/holdings.key';
$consortium_bibs_keys_deleted = $baseurl . '/consortium_bibs_deleted/consortiumbibs.key';

$consortium_holdings_keys_loader = IO::File->new("> $consortium_holdings_keys_deleted") || die "cannot open $consortium_holdings_keys_deleted for writing: $!\n";
binmode( $consortium_holdings_keys_loader, ':utf8' );
flock($consortium_holdings_keys_loader, 2);

$consortium_bibs_keys_loader = IO::File->new("> $consortium_bibs_keys_deleted") || die "cannot open $consortium_bibs_keys_deleted for writing: $!\n";
binmode( $consortium_bibs_keys_loader, ':utf8' );
flock($consortium_bibs_keys_loader, 2);

    print $consortium_holdings_keys_loader $mysql_holdings_keys . "\n";
    print $consortium_bibs_keys_loader $fields_array_string . "\n";

        $consortium_holdings_keys_loader->autoflush(1);
        close($consortium_holdings_keys_loader, 2) || "cannot close $consortium_holdings_keys_loader: $!";

        $consortium_bibs_keys_loader->autoflush(1);
        close($consortium_bibs_keys_loader, 2) || "cannot close $consortium_bibs_keys_loader: $!";
}

$limit_start = 0;
$rownumber = 1;
while($rownumber > 0) {

        $querystring = "SELECT * from $this_table
            WHERE $this_table.solr_update >= ?
            AND bib_status = 'x'
            LIMIT ?, 10000";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute($last_update_time, $limit_start)
            or die("couldn't execute statement: ". $dbh->errstr);

$limit_start += 10000;
$rownumber = $query->rows();

    print "rownumber: " . $rownumber . "\n";

while(@query_array = $query->fetchrow_array()) {
    $int = $recordcount / 10000;

    if ($int =~ m/^\d+$/) {

        if($recordcount > 0) {

        $holdings_batch_loader->autoflush(1);
        close($holdings_batch_loader, 2) || "cannot close $holdings_batch_loader: $!";

if ($consortium ne '') {
        $consortium_holdings_batch_loader->autoflush(1);
        close($consortium_holdings_batch_loader, 2) || "cannot close $consortium_holdings_batch_loader: $!";

        $consortium_bibs_batch_loader->autoflush(1);
        close($consortium_bibs_batch_loader, 2) || "cannot close $consortium_bibs_batch_loader: $!";
}

        }

$holdings_batch_deleted = $baseurl . '/holdings_deleted/holdings' . $recordcount .  '.txt';
$holdings_batch_loader = IO::File->new("> $holdings_batch_deleted") || die "cannot open $holdings_batch_deleted for writing: $!\n";
binmode( $holdings_batch_loader, ':utf8' );
flock($holdings_batch_loader, 2);


if ($consortium ne '') {
$consortium_holdings_batch_deleted = $baseurl . '/consortium_holdings_deleted/holdings' . $recordcount .  '.txt';
$consortium_bibs_batch_deleted = $baseurl . '/consortium_bibs_deleted/consortium_bibs_deleted' . $recordcount .  '.txt';

$consortium_holdings_batch_loader = IO::File->new("> $consortium_holdings_batch_deleted") || die "cannot open $consortium_holdings_batch_deleted for writing: $!\n";
binmode( $consortium_holdings_batch_loader, ':utf8' );
flock($consortium_holdings_batch_loader, 2);

$consortium_bibs_batch_loader = IO::File->new("> $consortium_bibs_batch_deleted") || die "cannot open $consortium_bibs_batch_deleted for writing: $!\n";
binmode( $consortium_bibs_batch_loader, ':utf8' );
flock($consortium_bibs_batch_loader, 2);
}

    }

$recordcount++;

pop(@query_array);

foreach(@query_array) {
    s/\t/\\t/g;
}

@blocs = split("\n", $query_array[$bibs2holdings_hash{'bloc'}]);

foreach $this_bloc (@blocs) {
        @holdings_array = ();

        push(@holdings_array, $query_array[$bibs2holdings_hash{'local_control_number'}], $query_array[$bibs2holdings_hash{'universal_identifier'}], $query_array[$bibs2holdings_hash{'worktitles'}], $query_array[$bibs2holdings_hash{'bib_status'}], $query_array[$bibs2holdings_hash{'formats'}], $this_bloc, $query_array[$bibs2holdings_hash{'record_owner'}]);


        $query_array_string = join("\t", @query_array);
        $query_array_string =~ s/\\\t/\\ \t/g;
        $query_array_string =~ s/\n/\\n/g;

        $holdings_array_string = join("\t", @holdings_array);
        $holdings_array_string =~ s/\\\t/\\ \t/g;
        $holdings_array_string =~ s/\n/\\n/g;

        print $holdings_batch_loader $holdings_array_string . "\n";

if ($consortium ne '') {
        print $consortium_holdings_batch_loader $holdings_array_string . "\n";
        print $consortium_bibs_batch_loader $query_array_string . "\n";
}

        $line_count++;
        }

    }

}

$query->finish();

if($recordcount > 0) {

        $mysql_holdings_keys = join(", ", @holdings_keys);

        $holdings_batch_loader->autoflush(1);
        close($holdings_batch_loader, 2) || "cannot close $holdings_batch_loader: $!";

if ($consortium ne '') {

        $consortium_holdings_batch_loader->autoflush(1);
        close($consortium_holdings_batch_loader, 2) || "cannot close $consortium_holdings_batch_loader: $!";

        $consortium_bibs_batch_loader->autoflush(1);
        close($consortium_bibs_batch_loader, 2) || "cannot close $consortium_bibs_batch_loader: $!";
}

####END: CREATING FILES FOR LOAD INTO LOCAL HOLDINGS TABLE AND CONSORTIUM BIBS AND HOLDINGS TABLES

#####START: LOADING FILES INTO LOCAL HOLDINGS TABLE

    $where = $baseurl . '/holdings_textfiles';

while (defined($next_file = <$where/*.txt>)) {

   $sth4 = $dbh->do("LOAD DATA INFILE '" . $next_file . "' REPLACE INTO TABLE $db_local_tables_hash{'holdings_record_table'} FIELDS TERMINATED BY '\t' (" . $mysql_holdings_keys . ")") || print "died again sth3 local\n";

unlink($next_file);

}

unlink($holdings_keys_deleted);

}

#####END: LOADING FILES INTO LOCAL HOLDINGS TABLE

if ($consortium ne '') {

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_external_update_time = "$HOUR:$MIN:$SEC";

}

$dbh->disconnect;

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_time = "$HOUR:$MIN:$SEC";

print "Start time: " . $start_time . "\nEnd time: " . $end_time . "\n";

#SUBROUTINES

sub hashes_and_arrays {

$hash_files = $baseurl . '/hashfiles';

while (defined($next_hash_file = <$hash_files/*.pl>)) {
require $next_hash_file;
}

}

